<?php

require_once '../database/querys.php';

class DBConnector
{
    /* singleton */
   
    private static $instance;

    public static function getInstance()
    {
        if (!isset(self::$instance))
        {
            $c = __CLASS__;
            self::$instance = new $c;
        }

        return self::$instance;
    }
   
    // instance variables
   
    private $connection;
    private $db_fields;

    // constructor
   
    private function __construct()
    {
        // Parse without sections
        $this->db_fields = parse_ini_file("/home/altran/.php-mysql-cpe.ini");
    }

    /**
     *  Connects to a mysql database.
     *  NOTE: in order to be secure, the fields to be used to
     *  connect to the database should be in the path:
     *
     *  ~/.php-mysql-cpe.ini
     */
    private function connect()
    {
        /// Create connection
        $this->connection = mysqli_connect($this->db_fields['url'],$this->db_fields['user'],
        $this->db_fields['pass'],$this->db_fields['db']);

        // -> DESCOMENTAR AQUI
        // -> NOTA verificar se o user e pass sao mesmo "altran" e "altran"
	// $this->connection = mysqli_connect("192.168.2.31","altran2","altran","cpe_real_data_v2");        
	$this->connection = mysqli_connect("bbnm-altran.no-ip.org","altran2","altran","cpe_real_data_v2");

        // Check connection
        if (mysqli_connect_errno())
        {
           die('Could not connect to mysql. Contact admin to resolve problems.');
        }
    }

    private function close()
    {  
        mysql_close($this->connection);
    }
   
    /* Methods */

    /**
     *  Returns all the channels from a given cpe.
     *  @param  cpe_id  The cpe id.
     *  @return The list of the different channels.
     *
     *  NOTE: return format (json):
     *  ["1","2","3","4","5","6","7","8","9","10","11","12","13"]
     */
    public function getChannels($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getChannelsQuery($cpe_id);
        $result = mysqli_query($this->connection,$query);
       
        $rows = array();
        while($row = mysqli_fetch_array($result))
        {
            array_push($rows, $row[Querys::$F_NOISE_CHANNEL]);
        }

        return $rows;
    }

    /**
     *  Returns the networks ssid and AP's macs from the last time entry
     *  from a given cpe.
     *  @param  cpe_id  The cpe id.
     *  @return most recent networks.
     *
     *  NOTE: return format (json):
     *  {"ZON-55E4D6":["0005CA55E4D6"],"VODA-0EDB03":["0024890EDB03"]}
     */
    public function getMostRecentNetworksAndMacs($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getMostRecentNetworksAndMacsQuery($cpe_id);
        $result = mysqli_query($this->connection,$query);
   
        $data = array();
        while($row = mysqli_fetch_array($result))
        {
            $key = $row[Querys::$F_WIFI_SSID];
            if (! array_key_exists($key, $data))
            {
                $data[$key] = array();
            }
            array_push($data[$key], $row[Querys::$F_WIFI_MAC]);
        }

        return $data;
    }
   
    /**
     *  Returns information about all networks and access
     *  points from a given cpe.
     *  Information: ssid of the network, mac of the access
     *  points for that network and the first and last
     *  ocorrences of that AP.
     *  @param  cpe_id  The cpe id.
     *  @return The information about the networks.
     *
     *  NOTE: return format (json):
     *  {"ZON-55E4D6":["0005CA55E4D6"],"VODA-0EDB03":["0024890EDB03"]}
     *  
     *  TODO: really really really really slow...
     */
    public function getAllNetworksInfo($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }

        $query = Querys::getAllNetworksInfoQuery($cpe_id);
        echo $query;
        $result = mysqli_query($this->connection,$query);

        $data = array();
        while($row = mysqli_fetch_array($result))
        {
            $key = $row[Querys::$F_WIFI_SSID];
            if (! array_key_exists($key, $data))
            {
                $data[$key] = array();
            }

            $node = array();
            $node[Querys::$F_WIFI_MAC] = $row[Querys::$F_WIFI_MAC];
            $node['first'] = $row['first'];
            $node['last'] = $row['last'];

            array_push($data[$key], $node);
        }

        return $data;
    }

    /**
     *  Returns noise data to a given cpe of a certain channels
     *  during an interval of time.
     *  @param  cpe_id          The cpe id.
     *  @param  channel_regex   The channels regular expression.
     *  @param  start_time      The start datetime of the data.
     *  @param  end_time        The end datetime of the data.
     *  @return The noise data.
     */
    public function getNoiseDataHour($cpe_id, $regex_channel, $start_time, $end_time)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getNoiseDataHourQuery($cpe_id, $regex_channel, $start_time, $end_time);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();
        $data[Querys::$F_NOISE_TIME] = array();  

        while($row = mysqli_fetch_array($result))
        {
            $time = $row[Querys::$F_NOISE_TIME];
            $channel = $row[Querys::$F_NOISE_CHANNEL];
            $noise = $row[Querys::$F_NOISE_TOTAL_NOISE];

            if ( ! in_array($time, $data[Querys::$F_NOISE_TIME]) )
            {
                array_push($data[Querys::$F_NOISE_TIME], $time);
            }

            if ( ! array_key_exists($channel, $data))
            {
                $data[$channel] = array();
            }

            array_push($data[$channel], $noise);
        }    
           
        return $data;
    }

    /**
     *  Returns noise data to a given cpe of a certain channels
     *  during an interval of time.
     *  @param  cpe_id          The cpe id.
     *  @param  channel_regex   The channels regular expression.
     *  @param  start_time      The start datetime of the data.
     *  @param  end_time        The end datetime of the data.
     *  @return The noise data.
     */
    // -> Acrescentei aqui
    public function getNoiseDataMinute($cpe_id, $regex_channel, $start_time, $end_time)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getNoiseDataMinuteQuery($cpe_id, $regex_channel, $start_time, $end_time);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();
        $data[Querys::$F_NOISE_TIME] = array();  

        while($row = mysqli_fetch_array($result))
        {
            $time = $row[Querys::$F_NOISE_TIME];
            $channel = $row[Querys::$F_NOISE_CHANNEL];
            $noise = $row[Querys::$F_NOISE_TOTAL_NOISE];

            if ( ! in_array($time, $data[Querys::$F_NOISE_TIME]) )
            {
                array_push($data[Querys::$F_NOISE_TIME], $time);
            }

            if ( ! array_key_exists($channel, $data))
            {
                $data[$channel] = array();
            }

            array_push($data[$channel], $noise);
        }    
           
        return $data;
    }

    /**
     *  Returns all ids and zip codes from all cpes.
     *  @return The id and zip codes.
     *
     *  NOTE: return format (json):
     *  {"1":"6230-421","2":"1600-079","3":"4710-053"}
     */
    public function getAllZipCodes()
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getAllZipCodesQuery();
        $result = mysqli_query($this->connection,$query);
       
        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $id = $row[Querys::$F_CPE_ID];
            $zip = $row[Querys::$F_CPE_ZIP];

            $data[$id] = $zip;
        }
           
        return $data;
    }

    /**
     *  Returns the number of networks of each provider given cpe
     *  during an interval of time.
     *  @param  cpe_id          The cpe id.
     *  @param  start_time      The start datetime of the data.
     *  @param  end_time        The end datetime of the data.
     *  @return The number of networks data.
     */
    public function getWifiDataCountHour($cpe_id, $start_time, $end_time)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getWifiDataCountHourQuery($cpe_id, $start_time, $end_time);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();
        $data[Querys::$F_WIFI_TIME] = array();
        $data['Vodafone'] = array();
        $data['MEO'] = array();
        $data['ZON'] = array();
        $data['Others'] = array();

        while($row = mysqli_fetch_array($result))
        {
            array_push($data[Querys::$F_WIFI_TIME], $row[Querys::$F_WIFI_TIME]);
            array_push($data['Vodafone'], $row['Vodafone']);
            array_push($data['MEO'], $row['MEO']);
            array_push($data['ZON'], $row['ZON']);
            array_push($data['Others'], $row['Others']);
        }    
           
        return $data;
    }

    /**
     *  Returns the number of networks of each provider given cpe
     *  during an interval of time.
     *  @param  cpe_id          The cpe id.
     *  @param  start_time      The start datetime of the data.
     *  @param  end_time        The end datetime of the data.
     *  @return The number of networks data.
     */
    // -> Ascrescentei aqui
    public function getWifiDataCountMinute($cpe_id, $start_time, $end_time)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getWifiDataCountMinuteQuery($cpe_id, $start_time, $end_time);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();
        $data[Querys::$F_WIFI_TIME] = array();
        $data['Vodafone'] = array();
        $data['MEO'] = array();
        $data['ZON'] = array();
        $data['Others'] = array();

        while($row = mysqli_fetch_array($result))
        {
            array_push($data[Querys::$F_WIFI_TIME], $row[Querys::$F_WIFI_TIME]);
            array_push($data['Vodafone'], $row['Vodafone']);
            array_push($data['MEO'], $row['MEO']);
            array_push($data['ZON'], $row['ZON']);
            array_push($data['Others'], $row['Others']);
        }    
           
        return $data;
    }

    /**
     *  Returns the number of networks of each provider given cpe
     *  from the begin.
     *  @param  cpe_id  The cpe id.
     *  @return The number of networks data.
     */
    public function getWifiDataCountAllByDay($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getWifiDataCountAllByDayQuery($cpe_id, $start_time, $end_time);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();
        $data[Querys::$F_WIFI_TIME] = array();
        $data['Vodafone'] = array();
        $data['MEO'] = array();
        $data['ZON'] = array();
        $data['Others'] = array();

        while($row = mysqli_fetch_array($result))
        {
            array_push($data[Querys::$F_WIFI_TIME], $row[Querys::$F_WIFI_TIME]);
            array_push($data['Vodafone'], $row['Vodafone']);
            array_push($data['MEO'], $row['MEO']);
            array_push($data['ZON'], $row['ZON']);
            array_push($data['Others'], $row['Others']);
        }    
           
        return $data;
    }

    /**
     *  Returns the number of the non dismissed threshold Alerts
     *  (only for noise table), given a cpe
     *  @param cpe_id   The cpe id
     *  @return The num of alerts
     *
     *  NOTE: return format (json):
     *  "2367"
     */
    public function getNumThresholdAlerts($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getNumThresholdAlertsQuery($cpe_id);
        $result = mysqli_query($this->connection,$query);
       
        $row = mysqli_fetch_array($result);  
        return $row['num'];
    }

    /**
     *  Returns the time bounds of the Noise Table (the first and last
     *  ocurrences of the a given cpe on the table)
     *  @param cpe_id   The cpe id
     *  @return The first and last Ocurrences
     *
     *  NOTE: return format (json):
     *  {"first":"2014-08-01 00:00:00","last":"2014-11-30 23:00:00"}
     */
    public function getNoiseDataTimeBounds ($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getNoiseDataTimeBoundsQuery($cpe_id);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();
        $row = mysqli_fetch_array($result);

        $data['first'] = $row['first'];
        $data['last'] = $row['last'];

        return $data;
    }

    /**
     *  Returns the time bounds of the Wifi Table (the first and last
     *  ocurrences of the a given cpe on the table)
     *  @param cpe_id   The cpe id
     *  @return The first and last Ocurrences
     *
     *  NOTE: return format (json):
     *  {"first":"2014-08-01 00:00:00","last":"2014-11-30 23:00:00"}
     */
    public function getWifiDataTimeBounds ($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getWifiDataTimeBoundsQuery($cpe_id);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();
        $row = mysqli_fetch_array($result);

        $data['first'] = $row['first'];
        $data['last'] = $row['last'];

        return $data;
    }

    /**
     *
     */
    public function getCPEsByZip ($cpe_id)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getCPEsByZipQuery($cpe_id);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $id = $row[Querys::$F_CPE_ID];
            $ssid = $row[Querys::$F_CPE_SSID];

            $data[$id] = $ssid;
        }

        return $data;
    }

    public function getJuntasByZip ($cp)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }
       
        $query = Querys::getJuntasByZipQuery($cp);
        $result = mysqli_query($this->connection,$query);
       
        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $ruaid = $row[Querys::$F_ZONAS_RUAID];
            $junta = $row[Querys::$F_ZONAS_JUNTA];

            $data[$ruaid] = $junta;
        }

        return $data;
    }

    //Joao Rodrigues - SmartSearch ByZip
    public function getCPEsZipByZip ($zip)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }

        $query = Querys::getCPEsZipByZipQuery($zip);
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $zip = $row[Querys::$F_CPE_ZIP];
            $ssid = $row[Querys::$F_CPE_SSID];

            $data[$zip] = $ssid;
        }

        return $data;
    }

    //Joao Rodrigues - SmartSearch ByMac
    public function getCPEsMacByMac ($mac)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }

        $query = Querys::getCPEsMacByMacQuery($mac);
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $mac = $row[Querys::$F_CPE_MAC];
            $ssid = $row[Querys::$F_CPE_SSID];

            $data[$mac] = $ssid;
        }

        return $data;
    }

    //Joao Rodrigues
    public function getCPEInfoByZip ($zip)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }

        $query = Querys::getCPEInfoByZipQuery($zip);
        $result = mysqli_query($this->connection,$query);
        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $id = $row[Querys::$F_CPE_ID];
            $mac = $row[Querys::$F_CPE_MAC];
            $ssid = $row[Querys::$F_CPE_SSID];

            $data["cpe_id"] = $id;
            $data["mac"] = $mac;
            $data["ssid"] = $ssid;
        }

        return $data;
    }

    //Joao Rodrigues
    public function getCPEInfoByMac ($mac)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }

        $query = Querys::getCPEInfoByMacQuery($mac);
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $id = $row[Querys::$F_CPE_ID];
            $mac = $row[Querys::$F_CPE_MAC];
            $ssid = $row[Querys::$F_CPE_SSID];

            $data["cpe_id"] = $id;
            $data["mac"] = $mac;
            $data["ssid"] = $ssid;
        }

        return $data;
    }

    //Joao Rodrigues
    public function getAllCities ()
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }

        $query = Querys::getAllCitiesQuery();
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $city = $row[Querys::$F_CPE_CITY];
	    $city = iconv('windows-1252', 'utf-8', $row['city']);
            $data[] = $city; 
        }

        return $data;
    }

    //Joao Rodrigues
    public function getStreetsByCity ($city)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }


        $query = Querys::getStreetsByCityQuery(iconv('utf-8', 'windows-1252',$city));
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $street = array();
            $street[] = iconv('windows-1252', 'utf-8', $row['street']);
            $street[] = $row['zip'];
            
            $data[] = $street;
        }

        return $data;
    }

    public function getAccessByCity ($city)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }


        $query = Querys::getAccessByCityQuery(iconv('utf-8', 'windows-1252',$city));
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $street = array();
            $street[] = iconv('windows-1252', 'utf-8', $row['access']);
            $street[] = $row['zip'];
            
            $data[] = $street;
        }

        return $data;
    }

    //Joao Rodrigues
    public function getCPEsByStreet ($street)
    {
        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }


        $query = Querys::getCPEsByStreetQuery(iconv('utf-8', 'windows-1252', $street));
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $cpe = array();
            $cpe[] = $row['cpe_id'];;
            $cpe[] = $row['mac'];
            $cpe[] = $row['ssid'];
            
            $data[] = $cpe;
        }

        return $data;
    }

    //Joao Rodrigues
    public function getCPEFullInfo ($cpe_id){

        if (mysql_ping($this->connection) == false)
        {
            $this->connect();
        }


        $query = Querys::getCPEFullInfoQuery($cpe_id);
        $result = mysqli_query($this->connection,$query);

        $data = array();

        while($row = mysqli_fetch_array($result))
        {
            $data["cpe_id"] = $row[Querys::$F_CPE_ID];
            $data["mac"] = $row[Querys::$F_CPE_MAC];
            $data["ssid"] = $row[Querys::$F_CPE_SSID];
            $data["zip"] = $row[Querys::$F_CPE_ZIP];
            $data["street"] = iconv('windows-1252', 'utf-8', $row['street']);
            $data["city"] = iconv('windows-1252', 'utf-8', $row[Querys::$F_CPE_CITY]);
	    $data["access"] = $row[Querys::$F_CPE_ACCESS];
            $data["wlan_channel"] = $row[Querys::$F_CPE_WLAN_CHANNEL];
            $data["wlan_status"] = $row[Querys::$F_CPE_WLAN_STATUS];
            $data["wlan_enable"] = $row[Querys::$F_CPE_WLAN_ENABLE];
            $data["wlan_radioenable"] = $row[Querys::$F_CPE_WLAN_RADIOENABLE];
            $data["wlan_standard"] = $row[Querys::$F_CPE_WLAN_STANDARD];
            $data["wlan_channelmode"] = $row[Querys::$F_CPE_WLAN_CHANNELMODE];
            $data["wlan_ssidadvert"] = $row[Querys::$F_CPE_WLAN_SSIDADVERT];
        }

        return $data;
    }


}

?>
